FastExport is known for its lightning speed when it comes to exporting vast amounts of data from Teradata and transferring the data into flat files on either a mainframe or network-attached computer.
Basic fundamentals of FastExport
- FastExport EXPORTS data from Teradata. The reason they call it FastExport is because it takes data off of Teradata (Exports Data). FastExport does not import data into Teradata.Additionally, like BTEQ it can output multiple files in a single run.
- FastExport only supports the SELECT statement. The only DML statement that FastExport understands is SELECT. You SELECT the data you want exported and FastExport will take care of the rest.
- FastExport over BTEQ when Exporting Data of more than half a million+ rows. When a large amount of data is being exported, FastExport is recommended over BTEQ Export. The only drawback is the total number of FastLoads, FastExports, and MultiLoads that can run at the same time, which is limited to 15. BTEQ Export does not have this restriction. Of course, FastExport will work with less data, but the speed may not be much faster than BTEQ.
- FastExport supports multiple SELECT statements and multiple tables in a single run. You can have multiple SELECT statements with FastExport and each SELECT can join information up to 64 tables.
- FastExport supports conditional logic, conditional expressions, arithmetic calculations, and data conversions. FastExport is flexible and supports the above conditions, calculations, and conversions.
- FastExport does NOT support error files or error limits. FastExport does not record particular error types in a table. The FastExport utility will terminate after a certain number of errors have been encountered.
- FastExport supports user-written routines INMODs and OUTMODs. FastExport allows you write INMOD and OUTMOD routines so you can select, validate and preprocess the exported data.
How FastExport Works
When FastExport is invoked, the utility logs onto the Teradata database and retrieves the rows that are specified in the SELECT statement and puts them into SPOOL. From there, it must build blocks to send back to the client. In comparison, BTEQ starts sending rows immediate for storage into a file.
If the output data is sorted, FastExport may be required to redistribute the selected data two times across the AMP processors in order to build the blocks in the correct sequence.
Remember, a lot of rows fit into a 64K block and both the rows and the blocks must be sequenced. While all of this redistribution is occurring, BTEQ continues to send rows. FastExport is getting behind in the processing. However, when FastExport starts sending the rows back a block at a time, it quickly overtakes and passes BTEQ’s row at time processing.
Also, if there is a requirement to manipulate the data before storing it on the computer’s hard drive, an OUTMOD routine can be written to modify the result set after it is sent back to the client on either the mainframe or LAN. Just like the BASF commercial states, “We don’t make the products you buy, we make the products you buy better”. FastExport is designed off the same premise, it does not make the SQL SELECT statement faster, but it does take the SQL SELECT statement and processes the request with lighting fast parallel processing!
The Teradata RDBMS will only support a maximum of 15 simultaneous FastLoad, MultiLoad, or FastExport utility jobs. This maximum value is determined and configured by the DBS Control record. This value can be set from 0 to 15. When Teradata is initially installed, this value is set at 5.
The Teradata RDBMS will only support a maximum of 15 simultaneous FastLoad, MultiLoad, or FastExport utility jobs. This maximum value is determined and configured by the DBS Control record. This value can be set from 0 to 15. When Teradata is initially installed, this value is set at 5.
The reason for this limitation is that FastLoad, MultiLoad, and FastExport all use large blocks to transfer data. If more then 15 simultaneous jobs were supported, a saturation point could be reached on the availability of resources. In this case, Teradata does an excellent job of protecting system resources by queuing up additional FastLoad, MultiLoad, and FastExport jobs that are attempting to connect.
For example, if the maximum numbers of utilities on the Teradata system is reached and another job attempts to run that job does not start. This limitation should be viewed as a safety control feature. A tip for remembering how the load limit applies is this, “If the name of the load utility contains either the word “Fast” or the word “Load”, then there can be only a total of fifteen of them running at any one time”. BTEQ does not have this load limitation. FastExport is clearly the better choice when exporting data. However, if two many load jobs are running. BTEQ is an alternate choice for exporting data.
FastExport Supported SQL Commands
FastExport accepts the following Teradata SQL statements. Each has been placed in alphabetic order for your convenience.
SQL Commands
ALTER TABLE :- Change a column or table options of a table.
CHECKPOINT:- Add a checkpoint entry in the journal table.
COLLECT STATISTICS:- Collect statistics for one or more columns or indexes in a table.
COMMENT:- Store or retrieve a comment string for a particular object.
CREATE DATABASE:- Creates a new database.
CREATE TABLE:- Creates a new table.
CREATE VIEW:- Creates a new view.
CREATE MACRO:- Creates a new macro.
DATABASE:- Specify a default database for the session.
DELETE:- Delete rows from a table.
DELETE DATABASE:- Removes all tables, views, macros, and stored procedures from a database.
DROP DATABASE:- Drops a database.
GIVE:- Transfer ownership of a database or user to another user.
GRANT:- Grant access privileges to an object.
MODIFY DATABASE:- Change the options for a database.
RENAME:- Change the name of a table, view, or macro.
REPLACE MACRO:- Change a macro.
REPLACE VIEW:- Change a view.
REVOKE:- Revoke privileges to an object.
SET SESSION COLLATION:- Override the collation specification during the current session.
UPDATE:- Change a column value of an existing row or rows in a table.
FastExport Support and Task Commands
FastExport accepts both FastExport commands and a subset of SQL statements. The FastExport commands can be broken down into support and task activities. The table below highlights the key FastExport commands and their definitions. These commands provide flexibility and control during the export process.
Support Environment Commands
ACCEPT:- Allows the value of utility variables to be accepted directly from a file or from environmental variables.
DATEFORM:- Specifies the style of the DATE data types for FastExport.
DISPLAY: -Writes messages to the specific location.
ELSE:- Used in conjunction with the IF statement. ELSE commands and statements will execute when a proceeding IF condition is false.
ENDIF:- Used in conjunction with the IF or ELSE statements. Delimits the commands that were subject to previous IF or ELSE conditions.
IF:- Introduces a conditional expression. If true then execution of subsequent commands will happen.
LOGOFF:- Disconnects all FastExport active sessions and terminates FastExport.
LOGON:- LOGON command or string used to connect sessions established through the FastExport utility.
LOGTABLE:- FastExport utilizes this to specify a restart log table. The purpose is for FastExport checkpoint information.
ROUTE MESSAGES:- Will route FastExport messages to an alternate destination.
RUN FILE:- Used to point to a file that FastExport is to use as standard input. This will Invoke the specified external file as the current source of utility and Teradata SQL commands.
SET:- Assigns a data type and value to a variable.
SYSTEM:- Suspends the FastExport utility temporarily and executes any valid local operating system command before returning.
Task Commands
BEGIN EXPORT:- Begins the export task and sets the specifications for the number of sessions with Teradata.
END EXPORT:- Ends the export task and initiates processing by Teradata.
EXPORT:- Provides two things which are:. The client destination and file format specifications for the export data retrieved from Teradata. A generated MultiLoad script file that can be used later to reload the export data back into Teradata
FIELD:- Constitutes a field in the input record section that provides data values for the SELECT statement.
FILLER:- Specifies a field in the input record that will not be sent to Teradata for processing. It is part of the input record to provide data values for the SELECT statement.
IMPORT:- Defines the file that provides the USING data values for the SELECT.
LAYOUT:- Specifies the data layout for a file. It contains a sequence of FIELD and FILLER commands. This is used to describe the import file that can optionally provide data values for the SELECT.
A FastExport in its Simplest Form
The hobby of racecar driving can be extremely frustrating, challenging, and rewarding all at the same time. I always remember my driving instructor coaching me during a practice session in a new car around a road course racetrack. He said to me, “Before you can learn to run, you need to learn how to walk.” This same philosophy can be applied when working with FastExport. If FastExport is broken into steps, then several things that appear to be complicated are really very simple. With this being stated, FastExport can be broken into the following steps:
· Logging onto Teradata
· Retrieves the rows you specify in your SELECT statement
· Exports the data to the specified file or OUTMOD routine
· Logs off of Teradata
/* Created by CoffingDW */
/* Setup the Fast Export Parameters */
LOGTABLE sql01.SWA_Log;
Creates the logtable -Required
.LOGON CDW/sql01,whynot;
Logon to Teradata
BEGIN EXPORT SESSIONS 12;
Begin the Export and set the number of sessions on Teradata
.EXPORT OUTFILE Student.txt
MODE RECORD FORMAT TEXT;
Defines the output file name. In addition, specifies the output mode and format (LAN – ONLY)
The SELECT defines the column used to create the export file.
NOTE: The selected columns for the export are being converted to character types. This will simplify the importing process into a different database.
/* Finish the Export Job and Write to File */
.END EXPORT;
.LOGOFF;
End the Export and logoff Teradata.
Figure 3-4
Sample FastExport Script
Now that the first steps have been taken to understand FastExport, the next step is to journey forward and review another example that shows builds upon what we have learned. In the script below, Teradata comment lines have been placed inside the script [/*. . . . */]. In addition, FastExport and SQL commands are written in upper case in order to highlight them. Another note is that the column names are listed vertically. The recommendation is to place the comma separator in front of the following column. Coding this way makes reading or debugging the script easier to accomplish.
/* ---------------------------------------------------------------- */
/* @(#) FASTEXPORT SCRIPT */
/* @(#) Version 1.1 */
/* @(#) Created by CoffingDW */
/* --------------------------------------------------------------------*/
ALWAYS GOOD TO IDENTIFY THE SCRIPT AND AUTHOR IN COMMENTS
/* Setup the Fast Export Parameters */
.LOGTABLE SQL01.CDW_Log;
.LOGON CDW/SQL01,whynot;
CREATE LOGTABLE AND LOGON;
.BEGIN EXPORT
SESSIONS 12;
BEGIN EXPORT STATEMENT.
SESSIONS 12;
.EXPORT OUTFILE Join_Export.txt
MODE RECORD FORMAT TEXT;
DEFINES THE OUTPUT FILE NAME. IN ADDITION, SPECIFIES THE OUTPUT MODE AND FORMAT(LAN – ONLY) MODE RECORD FORMAT TEXT;
THE SELECT PULLS DATA FROM TWO TABLES. IT IS GOOD TO QUALILY WHEN DOING A TWO-TABLE JOIN.
/* Finish the Export Job and Write to File */
.END EXPORT;
.LOGOFF;
END THE JOB AND LOGOFF TERADATA;
FastExport Modes and Formats
FastExport Modes
FastExport has two modes: RECORD or INDICATOR. In the mainframe world, only use RECORD mode. In the UNIX or LAN environment, RECORD mode is the default, but you can use INDICATOR mode if desired. The difference between the two modes is INDICATOR mode will set the indicator bits to 1 for column values containing NULLS.
Both modes return data in a client internal format with variable-length records. Each individual record has a value for all of the columns specified by the SELECT statement. All variable-length columns are preceded by a two-byte control value indicating the length of the column data. NULL columns have a value that is appropriate for the column data type. Remember, INDICATOR mode will set bit flags that identify the columns that have a null value.
FastExport Formats
FastExport has many possible formats in the UNIX or LAN environment. The FORMAT statement specifies the format for each record being exported which are:
· FASTLOAD
· BINARY
· TEXT
· UNFORMAT
The default FORMAT is FASTLOAD in a UNIX or LAN environment.
FASTLOAD Format is a two-byte integer, followed by the data, followed by an end-of-record marker. It is called FASTLOAD because the data is exported in a format ready for FASTLOAD.
BINARY Format is a two-byte integer, followed by data.
TEXT is an arbitrary number of bytes followed by an end-of-record marker.
UNFORMAT is exported as it is received from CLIv2 without any client modifications.
A FastExport Script Using Binary Mode
/* --------------------------------------------------------------*/
/* @(#) FASTEXPORT SCRIPT */
/* @(#) Version 1.1 */
/* @(#) Created by CoffingDW */
/* --------------------------------------------------------------*/
COMMENTS
/* Setup the Fast Export Parameters */
.LOGTABLE SQL01.SWA_LOG;
.LOGON CDW/Sql101,whynot;
CREATE LOGTABLE AND LOGON TO TERADATA
.BEGIN EXPORT
SESSIONS 12;
BEGIN EXPORT STATEMENT;
.EXPORT OUTFILE CDW_Export.txt
MODE RECORD FORMAT TEXT;
NAME THE OUTPUT FILE AND SET THE FORMAT TO BINARY;
THE SELECT PULLS DATA FROM TWO TABLES. IT IS GOOD TO QUALILY WHEN DOING A TWO-TABLE JOIN.
/* Finish the Export Job and Write to File */
.END EXPORT;
.LOGOFF;
END THE JOB;
Figure 3-6
CREATE SET TABLE MYUSER.STUDENT_T ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
stundent_id VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
last_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
first_name VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC,
class_code VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
grade_pt VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( stundent_id );
Exporting data in variable text format [,deliminated]:
.LOGTABLE myuser.yogita_fexp4;
.LOGON 127.0.0.1/MYUSER,MYUSER1;
DATABASE MYUSER;
.BEGIN EXPORT SESSIONS 2;
.EXPORT OUTFILE C:\TEST\student4.txt
MODE RECORD format text;
SELECT
CAST (stundent_id AS VARCHAR(20)) ||','||
CAST (last_name as VARCHAR(20)) ||','||
CAST (first_name as VARCHAR(14)) AS output_data
FROM STUDENT_T;
.END EXPORT;
.LOGOFF;
Exporting FASTLOAD data :
.LOGTABLE myuser.yogita_fexp7;
.LOGON 127.0.0.1/MYUSER,MYUSER1;
DATABASE MYUSER;
.BEGIN EXPORT SESSIONS 2;
.EXPORT OUTFILE C:\TEST\student7.txt format FASTLOAD;
SELECT * FROM STUDENT_T;
.END EXPORT;
.LOGOFF;
EXPORTING INTO MULTIPLE FILE :
.logon 127.0.0.1/myuser,muyser1;
.BEGIN EXPORT < >
.EXPORT OUTFILE <FILENAME1>
<SELECT STATEMENTS>
.END EXPORT
.BEGIN EXPORT <>
.EXPORT OUTFILE <FILENAME2>
<SELECT STATEMENTS>
.END EXPORT
.logoff;
Passing Paramenters to Fastexports:
There are two ways we Can pass parameters
Internal Parameters :
.IF ’&SYSDAY’ = ’Fri’ THEN;
14:10:28 - FRI MAY 09, 1993
UTY2402 Previous statement modified to:
0004 .IF ’FRI’ = ’Fri’ THEN;
0005.RUN FILE UTNTS38;
0006 .ENDIF;
.IF ’&SYSDAY’ = ’Fri’ THEN;
14:10:28 - FRI MAY 09, 1993
UTY2402 Previous statement modified to:
0004 .IF ’FRI’ = ’Fri’ THEN;
0005.RUN FILE UTNTS38;
0006 .ENDIF;
External Parameters:
.Accept accepts single record, Import accepts multiple records.
.logtable yogita_fexplog;
.RUN FILE_logon;
.SET cityname TO 'los Angeless';
.SET ZIPCODE TO 80006;
.BEGIN EXPORT SESSION 4;
.EXPORT OUTFILE custacct_data;
SELECT a.account_number,
c.last_name,
c.first_name,
A.balance_current
FROM accounts A INNER JOIN
Accounts_custoer AC INNER JOIN
ON c.customer_number = AC.customer_number
ON A.account_number=AC.account_number
WHERE a.city = '&cityname'
and a.zip_code = &zipcode
ORDER BY 1;
.END EXPORT
.LOGOFF;
FastExport Example
The following FastExport job script example executes a single SELECT statement
and returns the results to a data set on the client system:
.LOGTABLE utillog ; /* define restart log */
.LOGON tdpz/user, pswd ; /* DBC logon string */
.BEGIN EXPORT /* specify export function */
SESSIONS 20; /* number of sessions to be used */
.LAYOUT UsingData ; /* define the input data */
.FIELD ProjId * Char(8) ; /* values for the SELECT */
.FIELD WkEnd * Date ; /* constraint clause. */
.IMPORT INFILE ddname1 /* identify the file that */
LAYOUT UsingData ; /* contains the input data */
.EXPORT OUTFILE ddname2 ; /* identify the destination */
/* file for exported data */
SELECT EmpNo, Hours
FROM CHARGES /* provide the SQL SELECT */
WHERE WkEnd = :WkEnd /* statement with values */
AND Proj_ID = :ProjId /* provided by the IMPORT */
ORDER BY EmpNo ; /* command */
.END EXPORT ; /* terminate the export */
.LOGOFF ; /* disconnect from the DBS */
TRY fastexport EXAMPLE.
Here is the Source table
--------------------------------------
show table Dealer_sale_service_station;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
-------------------------------------------------------------------
CREATE SET TABLE CSS.Dealer_sale_service_station ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
SS_station VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
SS_stationid SMALLINT,
SS_Emailid VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
SS_Address VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
SS_Phone VARBYTE(15))
UNIQUE PRIMARY INDEX ( SS_stationid )
UNIQUE INDEX ( SS_Phone );
Here is the Destination table
--------------------------------------
show table Dealer_sale_service_tmp;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
--------------------------------------------------------------------
CREATE SET TABLE CSS.Dealer_sale_service_tmp ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
SS_station VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
SS_stationid SMALLINT,
SS_Emailid VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
SS_Address VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
SS_Phone VARBYTE(15))
UNIQUE PRIMARY INDEX ( SS_stationid );
Here is the fastexport script.
--------------------------------------
.logtable css.css_logtbl;
.logon leo/dbc,dbc;
database css;
.begin export;
.export outfile Dealer_sale_service_station.fout format fastload;
sel * from Dealer_sale_service_station;
.end export;
Here is the fastload script:
--------------------------------------
.sessions 2;
.errlimit 2;
.logon leo/dbc,dbc
database css;
DEFINE FILE=./Dealer_sale_service_station.fout;
begin loading Dealer_sale_service_tmp errorfiles error_adsss,
error_bdsss;
insert into Dealer_sale_service_tmp.*;
end loading;
.logoff
.quit
When Teradata FastExport is invoked, the utility executes the FastExport commands and Teradata SQL statements in the FastExport job script. These direct FastExport to:
1. Log on to Teradata Database for a specified number of sessions, using username, password, and tdpid/ acctid information.
2. Retrieve the specified data from Teradata Database, in accordance with format and selection specifications.
3. Export the data to the specified file or OUTMOD routine on a client system.
4. Log off of Teradata Database.
cat lab512.fxp
.LOGTABLE Restartlog512_fxp ;
.LOGON u4455/tljc30,tljc30 ;
.BEGIN EXPORT;
.EXPORT OUTFILE data5_1;
SELECT T.trans_number
,A.account_number
,A.number
,A.street
,A.city
,A.state
,A.zip_code
FROM AU.Accounts A
INNER JOIN AU.Trans T
ON A.Account_number = T.Account_number;
.END EXPORT;
.LOGOFF;
fexp < lab512.fxp
No comments:
Post a Comment